package com.gdssoft.pdaweb.dao.water.impl;

/*
 *  捷达世软件（深圳）有限公司
 *  Copyright (C) 2008-2010 ShenZhen JieDaShi Software Limited Company.
 */
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;

import com.gdssoft.core.command.QueryFilter;
import com.gdssoft.core.dao.impl.BaseDaoImpl;
import com.gdssoft.core.util.DateUtil;
import com.gdssoft.pdaweb.dao.water.PdaWaterListDao;
import com.gdssoft.pdaweb.model.water.PdaWaterList;
import com.gdssoft.pdaweb.model.water.PdaWaterMeter;
import com.gdssoft.pdaweb.model.water.WaterMeterPlan;

@SuppressWarnings("unchecked")
public class PdaWaterListDaoImpl extends BaseDaoImpl<PdaWaterList> implements
		PdaWaterListDao {

	private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");

	public PdaWaterListDaoImpl() {
		super(PdaWaterList.class);
	}

	@Override
	public boolean checkAmidExist(String amId,String depIdS) {
		int count = findByHql("from PdaWaterMeter m where m.amId='" + amId+"' and m.department.depId in ("+depIdS+")").size();
		if (count > 0) {
			return true;
		}
		return false;
	}

	@Override
	public boolean recodExist(String amId, String importMonth) {
		int count = findByHql(
				"from PdaWaterList l where l.amId=? and l.month=?",
				new Object[] { amId, importMonth }).size();
		if (count > 0) {
			return true;
		}
		return false;
	}

	@Override
	public Long getDegreeByTime(String amId, String month) {
		long degree = -1;
		List<PdaWaterList> list = findByHql("from PdaWaterList pl where pl.amId=? and pl.month=?", new Object[]{amId,month});
		if(list.size()>0){
			degree = list.get(0).getDegree();
		}/*else{
//			String hqlString = "select pl from PdaWaterList pl where pl.amId=(" +
//					"select po.amId from PdaWaterOperate po where po.oldAmId=?) and pl.month=?";
			String hqlString = "select pl from PdaWaterList pl where pl.amId=(" +
					"select po.oldAmId from PdaWaterOperate po where po.amId=?) and pl.month=?";
			List<PdaWaterList> list_old = findByHql(hqlString,new Object[]{amId,month});
			if(list_old.size()>0){
				degree = list.get(0).getDegree();
			}
		}*/
		return degree;
	}

	@Override
	public int isCurAdd(String amId ,String month){
		int count = findByHql("from PdaWaterOperate o where o.amId=? and o.isAlert=?",new Object[]{amId,1}).size();
		return count;
	}

	/**
	 * sqlAndArgsForQueryOrCount 得到一致的用于查询记录数和记录的sql以及参数list
	 * 
	 * @author F3229233 2013-1-15 下午3:21:50
	 * @param pl
	 * @return Map(包含从from开始的sql及条件参数list)
	 */
	private Map<String, Object> sqlAndArgsForQueryOrCount(PdaWaterList pl) {
		List<Object> args = new ArrayList<Object>();

		StringBuffer sql = new StringBuffer(
				" FROM PDA_WATER_LIST pl"
						+ " JOIN PDA_WATER_METER pm ON pl.METER_ID=pm.METER_ID"
						+ " JOIN PDA_APARMENT pa ON pm.APARMENT_ID=pa.APARMENT_ID WHERE pl.MONTH=? ");

		String monthStr = pl.getMonth() == null ? sdf.format(new Date()) : pl
				.getMonth();
		pl.setMonth(monthStr);
		args.add(pl.getMonth());

		if (pl.getStatus() != null && !pl.getStatus().isEmpty()) { // 条件：状态
			if (pl.getStatus().equals("0") || pl.getStatus().equals("1")) {
				sql.append("AND pl.STATUS=? ");
				args.add(pl.getStatus());
			} else {
				sql.append("AND pl.STATUS IN (2,3,4) ");
			}
		}
		if (pl.getAmId() != null && !pl.getAmId().isEmpty()) {
			sql.append("AND pl.METER_ID LIKE ? ");
			args.add(pl.getAmId() == null ? "%" : pl.getAmId() + "%");
		}
		if (pl.getPdaWaterMeter() != null) {
			if (pl.getPdaWaterMeter().getClientId() != null
					&& !pl.getPdaWaterMeter().getClientId().isEmpty()) {
				sql.append("AND pm.CLIENT_ID LIKE ? ");
				args.add(pl.getPdaWaterMeter().getClientId() + "%");
			}
			if (pl.getPdaWaterMeter().getDistrictId() != null
					&& !pl.getPdaWaterMeter().getDistrictId().isEmpty()) {
				sql.append("AND pm.DISTRICT_ID LIKE ? ");
				args.add(pl.getPdaWaterMeter().getDistrictId() + "%");
			}
			if (pl.getPdaWaterMeter().getBuildingId() != null
					&& !pl.getPdaWaterMeter().getBuildingId().isEmpty()) {
				sql.append("AND pm.BUILDING_ID LIKE ? ");
				args.add(pl.getPdaWaterMeter().getBuildingId() + "%");
			}
			if (pl.getPdaWaterMeter().getAparmentId() != null
					&& !pl.getPdaWaterMeter().getAparmentId().isEmpty()) {
				sql.append("AND pm.APARMENT_ID LIKE ?");
				args.add(pl.getPdaWaterMeter().getAparmentId() + "%");
			}
		}

		Map<String, Object> map = new HashMap<String, Object>();
		map.put("SQL", sql);
		map.put("ARGS", args);
		return map;
	}

	@Override
	public int queryCount(PdaWaterList pl) {
		Map sqlMap = sqlAndArgsForQueryOrCount(pl);
		String sql = "SELECT COUNT(1) " + sqlMap.get("SQL").toString();
		List<Object> args = (List<Object>) sqlMap.get("ARGS");
		return jdbcTemplate.queryForInt(sql, args.toArray());
	}

	@Override
	public List<PdaWaterList> query(PdaWaterList pl, int start, int to) {
		List<PdaWaterList> list = new ArrayList<PdaWaterList>();

		String monthStr = pl.getMonth() == null ? sdf.format(new Date()) : pl
				.getMonth();
		pl.setMonth(monthStr);

		Map sqlMap = sqlAndArgsForQueryOrCount(pl);

		String sql = "SELECT B.* FROM (SELECT ROWNUM rn, A.* FROM("
				+ "SELECT nvl(pll.DEGREE,-1) AS LASTDEGREE,pl.* FROM PDA_WATER_LIST pll RIGHT JOIN ("
				+ "SELECT pl.ID,pl.METER_ID,pl.DEGREE,pm.DISTRICT_ID,pm.BUILDING_ID,pm.APARMENT_ID,pa.NAME,pl.STATUS,pl.RECORDER_ID"
				+ sqlMap.get("SQL").toString()
				+ ") pl on pl.METER_ID = pll.METER_ID and pll.MONTH=?"
				+ ") A WHERE ROWNUM <= " + to + ") B WHERE rn > " + start;

		List<Object> args = (List<Object>) sqlMap.get("ARGS");
		args.add(DateUtil.getLastMonthStr(monthStr));

		try {
			List rows = jdbcTemplate.queryForList(sql, args.toArray());
			Iterator it = rows.iterator();
			while (it.hasNext()) {
				Map map = (Map) it.next();
				PdaWaterList newpl = new PdaWaterList();
				PdaWaterMeter pm = new PdaWaterMeter();
				pm.setDistrictId(map.get("DISTRICT_ID").toString());
				pm.setBuildingId(map.get("BUILDING_ID").toString());
				pm.setAparmentId(map.get("APARMENT_ID").toString());
				pm.setAparmentName(map.get("NAME").toString());
				newpl.setPdaWaterMeter(pm);
				newpl.setMonth(monthStr);
				newpl.setId(((BigDecimal) map.get("ID")).longValue());
				newpl.setAmId(map.get("METER_ID").toString());
				newpl.setStatus(map.get("STATUS").toString());
				newpl.setRecorderId(map.get("RECORDER_ID").toString());
				newpl.setLastDegree(((BigDecimal) map.get("LASTDEGREE"))
						.longValue());
				newpl.setDegree(((BigDecimal) map.get("DEGREE")).longValue());
				list.add(newpl);
			}
		} catch (Exception e) {
			logger.debug("PdaAmMeterList query error:" + e.getMessage());
		}
		return list;
	}

	@Override
	public boolean check(String ids, String status) {
		int count = jdbcTemplate.update("UPDATE PDA_WATER_LIST SET STATUS = "
				+ status + " WHERE ID IN (" + ids + ")");
		if (count == ids.split(",").length) {
			return true;
		}
		return false;
	}
	@Override
	public List<PdaWaterList> getPlanTask(String meterId, String month) {
		String hql = "from PdaWaterList where amId = '"+meterId+"' and month = '"+month+"'";
		List<PdaWaterList> ls = new ArrayList<PdaWaterList>();
		ls = this.findByHql(hql);
		if(ls.size()>0)
		{
		   return ls;
		}
		return new ArrayList<PdaWaterList>(); 
	}

	@Override
	public PdaWaterList getWaterListByMeterIDAndMonth(String meterID,
			String month) {
		String hql="from PdaWaterList where amId=? and month=?";
		return (PdaWaterList) this.findUnique(hql,new String[]{meterID, month});
	}

	@Override
	public boolean saveFixedUsage(PdaWaterList pdaWaterList) {
		String sql = "";
		if(pdaWaterList.getRemark().equals("")){
			sql = "update pda_water_list set status=0, fixedusage="
					+pdaWaterList.getFixedUsage()
					+" where id="
					+pdaWaterList.getId();
		}else{
			sql = "update pda_water_list set status=0, remark='"
					+ pdaWaterList.getRemark()
					+ "', fixedusage="
					+pdaWaterList.getFixedUsage()
					+" where id="
					+pdaWaterList.getId();
		}
		
		int count = jdbcTemplate.update(sql);
		if(count > 0){
			return true;
		}
		return false;
	}

	@Override
	public int getWaterPlanNumb(String month, String planName,
			String executor,String depId) {
		// TODO Auto-generated method stub
		 int numb=0;
		String sql = "SELECT COUNT(T.PLAN_ID) NUMB"
				+ "  FROM (SELECT P.PLAN_ID,"
				+ "               P.PLAN_NAME,"
				+ "               P.ACCOUNT_DAY,"
				+ "               P.METER_COUNT,"
				+ "               P.PLAN_STATUS,"
				+ "               P.CREATEDDT,"
				+ "               P.CREATEDBY,"
				+ "               P.LASTEDITDT,"
				+ "               P.LASTEDITBY,"
				+ "               U.STATUSP,"
				+ "               (SELECT DISTINCT Z.EXECUTOR"
				+ "                  FROM PDA_WATERMETER_MONTHPLAN Z"
				+ "                 WHERE Z.MONTH = ?"
				+ "                   AND Z.PLAN_ID = P.PLAN_ID) EXECUTOR"
				+ "          FROM PDA_WATERMETER_PLAN P,"
				+ "               (SELECT DISTINCT Q.PLAN_ID, MAX(S.STATUS) STATUSP"
				+ "                  FROM PDA_PLAN_DETAIL Q, PDA_WATER_LIST S,PDA_WATER_METER W "
				+ "                 WHERE Q.METER_ID = S.METER_ID AND W.METER_ID=S.METER_ID"
				+ "                   AND W.DEPID IN (" + depId
				+ ")AND S.MONTH = ?" + "                   AND S.STATUS <> '1'"
				+ "                 GROUP BY Q.PLAN_ID) U"
				+ "         WHERE P.PLAN_ID IN (U.PLAN_ID)) T"
				+ " WHERE T.PLAN_NAME LIKE '%' || ? || '%'"
				+ "   AND T.EXECUTOR LIKE '%' || ? || '%'";

		              String[] param=new String[]{month,month,planName,executor};
				
				try{
					List rows = jdbcTemplate.queryForList(sql,param);
					Iterator it = rows.iterator();
					while(it.hasNext()) {
					    Map map = (Map) it.next();
					    numb=Integer.parseInt(map.get("NUMB").toString());
					   
					}
				}catch(Exception e){
					logger.debug("getWaterPlan query error:"+e.getMessage());			
				}
				return numb;

	}

	
	@Override
	public boolean checkPlan(String ids, String status, String month) {
		// TODO Auto-generated method stub
		String sql="UPDATE PDA_WATER_LIST" 
				  +"     SET STATUS = '"+status+"'"  
				  +"   WHERE ID IN ("+ids+")";
		int count = jdbcTemplate.update(sql);
		if(count>0){
			return true;
		}
		return false;
	}	

	@Override
	public String getPdaWaterIdList(String ids, String month) {
		// TODO Auto-generated method stub
		String idsParam="";
		String sql="SELECT DISTINCT S.ID"  
				  +"                   FROM PDA_PLAN_DETAIL Q, PDA_WATER_LIST S" 
				  +"                 WHERE Q.METER_ID = S.METER_ID" 
				  +"                   AND S.MONTH = '"+month+"'"  
				  +"                   AND S.STATUS <> '1'"  
				  +"                   AND Q.PLAN_ID IN ("+ids+")";
		 try{
				List rows = jdbcTemplate.queryForList(sql);
				Iterator it = rows.iterator();
				while(it.hasNext()) {
				    Map map = (Map) it.next();
				    if(idsParam.equals("")){
				    	idsParam=map.get("ID").toString();
				    }else{
				    	idsParam=idsParam+","+map.get("ID").toString();
				    }				   
				}
			}catch(Exception e){
				logger.debug("getPdaWaterIdList query error:"+e.getMessage());			
			}

		return idsParam;
	}

	@Override
	public List<PdaWaterList> getCheckDetailSearch(String planId,
			String planName, String month, String executor, String fangId,
			String waterId, String dongId, String quId,String depId,QueryFilter filter) {
		Long planIdl=Long.parseLong(planId);
		// TODO Auto-generated method stub
		String sql="SELECT p"
				  +	"  FROM PdaWaterList AS p, PdaPlanDetail AS q"
				  + " WHERE q.meterId = p.amId"
				  + "   AND q.planId ="+planIdl+" AND p.status<>'1'"
				  + "   AND p.month='"+month+"'" 
				  + "   AND p.pdaWaterMeter.districtId LIKE '%'||'"+quId+"'||'%'"
				  + "   AND p.pdaWaterMeter.buildingId LIKE '%'||'"+dongId+"'||'%'"
				  + "   AND p.pdaWaterMeter.aparmentId LIKE '%'||'"+fangId+"'||'%'"
				  + "   AND p.amId LIKE '%'||'"+waterId+"'||'%'"
				  + "   AND p.pdaWaterMeter.department.depId IN ("+depId+")";	
			 Query query=this.getSession().createQuery(sql);
			 query.setFirstResult(filter.getPagingBean().getFirstResult());
			 query.setMaxResults(filter.getPagingBean().getPageSize());
		return query.list();
	}

	@Override
	public List<PdaWaterList> checkStatus(Long planId,String month) {
		List<PdaWaterList> listNew = new ArrayList<PdaWaterList>();
		String sql="SELECT q.ID\n" +
						"  FROM PDA_PLAN_DETAIL p, PDA_WATER_LIST q\n" + 
						" WHERE p.METER_ID = q.METER_ID\n" + 
						"   AND p.PLAN_ID = "+planId+"  "  +
						"   AND q.MONTH = '"+month+"' "    + 
						"   AND q.STATUS = '2' ";
		
		 try{
				List rows = jdbcTemplate.queryForList(sql);
				Iterator it = rows.iterator();
				while(it.hasNext()) {
				    Map map = (Map) it.next();
				    PdaWaterList newpl = new PdaWaterList();
//				    newpl.getVersion()(planId); 
//				    newpl.setLastEditBy(month);
				    newpl.setMonth(month);
				    listNew.add(newpl);
				}
			}catch(Exception e){
				logger.debug("getPdaWaterIdList query error:"+e.getMessage());			
			}
		 
		return listNew;
	}
	@Override
	public List<WaterMeterPlan> getWaterPlan(String month, String planName,
			String executor,String depId,QueryFilter filter) {
		// TODO Auto-generated method stub
		  List<WaterMeterPlan> list = new ArrayList<WaterMeterPlan>();
			
			String sql =" SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT T.PLAN_ID, T.PLAN_NAME, T.STATUSP, T.EXECUTOR" 
			           +	"  FROM (SELECT P.PLAN_ID,"  
			           + 	"               P.PLAN_NAME,"  
			           + 	"               P.ACCOUNT_DAY," 
			           + 	"               P.METER_COUNT,"  
			           + 	"               P.PLAN_STATUS,"  
			           + 	"               P.CREATEDDT,"  
			           + 	"               P.CREATEDBY,"  
			           + 	"               P.LASTEDITDT,"  
			           + 	"               P.LASTEDITBY," 
			           + 	"               U.STATUSP,"  
			           + 	"               (SELECT DISTINCT Z.EXECUTOR"  
			           + 	"                  FROM PDA_WATERMETER_MONTHPLAN Z"  
			           + 	"                 WHERE Z.MONTH = ?"  
			           + 	"                   AND Z.PLAN_ID = P.PLAN_ID) EXECUTOR"  
			           + 	"          FROM PDA_WATERMETER_PLAN P,"  
			           + 	"               (SELECT DISTINCT Q.PLAN_ID, MAX(S.STATUS) STATUSP"  
			           + 	"                  FROM PDA_PLAN_DETAIL Q, PDA_WATER_LIST S,PDA_WATER_METER W "  
			           + 	"                 WHERE Q.METER_ID = S.METER_ID AND W.METER_ID=S.METER_ID"  
			           + 	"                   AND W.DEPID IN ("+depId+")AND S.MONTH = ?"  
			           + 	"                   AND S.STATUS <> '1'"  
			           + 	"                 GROUP BY Q.PLAN_ID) U"  
			           + 	"         WHERE P.PLAN_ID IN (U.PLAN_ID)  ORDER BY P.PLAN_ID) T"  
			           + 	" WHERE T.PLAN_NAME LIKE '%' || ? || '%'"  
			           + 	"   AND T.EXECUTOR LIKE '%' || ? || '%')row_ WHERE  "
		           +    "   rownum <= "+(filter.getPagingBean().getFirstResult()+filter.getPagingBean().getPageSize())+") "
		           +    " WHERE rownum_ > "+filter.getPagingBean().getFirstResult()+"";

	              String[] param=new String[]{month,month,planName,executor};
			
			try{
				List rows = jdbcTemplate.queryForList(sql,param);
				Iterator it = rows.iterator();
				while(it.hasNext()) {
				    Map map = (Map) it.next();
				    WaterMeterPlan newpl = new WaterMeterPlan();
				    newpl.setPlanId(Long.parseLong(map.get("PLAN_ID").toString()));//計劃ID
				    newpl.setPlanName(map.get("PLAN_NAME").toString());//計劃名稱
				    newpl.setPlanStatus(Long.parseLong(map.get("STATUSP").toString()));//計劃表狀態，抓計劃表下所有電表狀態級別最高的
				    newpl.setExecutor(map.get("EXECUTOR").toString());//抄表人
				    newpl.setLastEditBy(month);//由於AmPlan沒有抄表年月，暫時用這個欄位存抄表年月			   
				    list.add(newpl);
				}
			}catch(Exception e){
				logger.debug("getWaterPlan query error:"+e.getMessage());			
			}
			return list;
	}

	@Override
	public int getCheckDetailSearchNumb(String planId, String planName,
			String month, String executor, String fangId, String waterId,
			String dongId, String quId, String depId) {
		// TODO Auto-generated method stub
		Long planIdl=Long.parseLong(planId);
		// TODO Auto-generated method stub
		String sql="SELECT p"
				  +	"  FROM PdaWaterList AS p, PdaPlanDetail AS q"
				  + " WHERE q.meterId = p.amId"
				  + "   AND q.planId ="+planIdl+" AND p.status<>'1'"
				  + "   AND p.month='"+month+"'" 
				  + "   AND p.pdaWaterMeter.districtId LIKE '%'||'"+quId+"'||'%'"
				  + "   AND p.pdaWaterMeter.buildingId LIKE '%'||'"+dongId+"'||'%'"
				  + "   AND p.pdaWaterMeter.aparmentId LIKE '%'||'"+fangId+"'||'%'"
				  + "   AND p.amId LIKE '%'||'"+waterId+"'||'%'"
				  + "   AND p.pdaWaterMeter.department.depId IN ("+depId+")";
		return this.findByHql(sql).size();

	}

	@Override
	public List<PdaWaterList> getList(Map params,String depIds) {
			 Query query = getQuery4getList( params, depIds);
			 query.setFirstResult(Integer.parseInt((String)params.get("start")));
			 query.setMaxResults(Integer.parseInt((String)params.get("limit")));
		return query.list();
	}

	@Override
	public int getListCount(Map params,String depIds) {
		return getQuery4getList( params, depIds).list().size();
	}
	
	//for function  getList(HttpServletRequest req,String depIds) and getListCount(HttpServletRequest req,String depIds)
	private Query getQuery4getList(Map params, String depIds) {
		String ss = ((String)params.get("Q_status_S_SIN"));
		String hql ="SELECT p FROM PdaWaterList AS p"
				  + " WHERE p.month=:month" 
				  + "   AND p.pdaWaterMeter.districtId LIKE :quId"
				  + "   AND p.pdaWaterMeter.buildingId LIKE :dongId"
				  + "   AND p.pdaWaterMeter.aparmentId LIKE :fangId"
				  + "   AND p.amId LIKE :amId"
				  + "   AND p.pdaWaterMeter.department.depId IN ("+depIds+")";	
		
		String degree = "";
		if(ss!=null){
			hql += "   AND p.status in ("+ss+")";
			hql += "   AND p.degree <> :degree";
			degree = (String)params.get("Q_degree_L_NEQ");
		}else{
			hql += "   AND p.degree = :degree";
			degree = (String)params.get("Q_degree_L_EQ");
		}
		
		return this.getSession().createQuery(hql)
				 .setParameter("amId", "%"+(String)params.get("Q_amId_S_LK")+"%")
				 .setParameter("month", (String)params.get("Q_month_S_LK"))
				 .setParameter("degree", Long.parseLong(degree))
				 .setParameter("quId", "%"+(String)params.get("Q_pdaWaterMeter.districtId_S_LK")+"%")
				 .setParameter("dongId", "%"+(String)params.get("Q_pdaWaterMeter.buildingId_S_LK")+"%")
				 .setParameter("fangId", "%"+(String)params.get("Q_pdaWaterMeter.aparmentId_S_LK")+"%");
	}

	
	
}
